package cn.com.callback

import groovy.json.*;
import groovy.swing.impl.DefaultAction;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.RequestDispatcher;
import cn.com.callback.CallBackInvoke;
import cn.com.callback.ServiceBackInvoke;
import cn.com.elite.EliteBean;
import java.security.MessageDigest;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import net.sf.json.JSONObject;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.wisdge.dataservice.Result;
import cn.com.elite.Util;

public class Job_BAGDHandle implements CallBackInvoke{
	private static final Log log=LogFactory.getLog(Job_BAGDHandle.class);
	public void doCallBack(EliteBean eliteBean,String callBack,Map map,String paramString){

		log.debug("-------------------------start  BAGDHandle------------------------");
		def GDCount = 0;
		def msg='';
		def samp='';
		try {
			//查询宝安工单处理时间为一个小时内的归档数据
			samp = "SELECT RELATION_ID,HANDLE_DESCRIPTION,REMARKS,CONVERT(varchar(100), START_TIME, 20) as START_TIME,CONVERT(varchar(100), FINISH_TIME, 20) as FINISH_TIME,WORKING_DAY,HANDLER,HANDLER_PHONE,RESPONSIBLE_PERSON,CONVERT(varchar(100), ARRIVED, 20) as ARRIVED FROM OPENQUERY(BAGWMID,'SELECT * from BAGW_MID.SZSW_WORKFLOW where 1=1 and FINISH_TIME is not null and CURSTATUS=''GW006004''') a LEFT JOIN objective o With (Nolock) on o.objective_guid=a.RELATION_ID where FINISH_TIME >DATEADD(HOUR, -1, GETDATE()) and o.curstep_id_map='VXGUM0'";

			List allBAGDList = eliteBean.chaxun_sql(samp, "dataSource");
			log.debug("查询一个小时内的宝安工单归档状态数据SQL:" + samp);

			def ListNum = allBAGDList.size();

			if(ListNum > 0){
				for(int i = 0; i<ListNum; i++) {
					Map BAGDMap = allBAGDList.get(i);

					def RELATION_ID = BAGDMap.get("RELATION_ID");
					def HANDLE_DESCRIPTION = BAGDMap.get("HANDLE_DESCRIPTION");
					def REMARKS = BAGDMap.get("REMARKS");
					def START_TIME = BAGDMap.get("START_TIME");
					def FINISH_TIME = BAGDMap.get("FINISH_TIME");
					def WORKING_DAY = BAGDMap.get("WORKING_DAY");
					def HANDLER = BAGDMap.get("HANDLER");
					def HANDLER_PHONE = BAGDMap.get("HANDLER_PHONE");
					def RESPONSIBLE_PERSON = BAGDMap.get("RESPONSIBLE_PERSON");
					def ARRIVED = BAGDMap.get("ARRIVED");

					//def updatesamp=eliteBean.update("update TABCswgdmerge set TABCswgdmerge.handledescription=case when '"+HANDLE_DESCRIPTION+"' ='null' then null else '"+HANDLE_DESCRIPTION+"' end,TABCswgdmerge.remarks=case when '"+REMARKS+"' ='null' then null else '"+REMARKS+"' end,TABCswgdmerge.starttime=case when '"+START_TIME+"' ='null' then null else '"+START_TIME+"' end,TABCswgdmerge.FINISHTIME=case when '"+FINISH_TIME+"' ='null' then null else '"+FINISH_TIME+"' end,TABCswgdmerge.workingday=case when '"+WORKING_DAY+"' ='null' then null else '"+WORKING_DAY+"' end,TABCswgdmerge.handlerby=case when '"+HANDLER+"' ='null' then null else '"+HANDLER+"' end,TABCswgdmerge.handlerphone=case when '"+HANDLER_PHONE+"' ='null' then null else '"+HANDLER_PHONE+"' end,TABCswgdmerge.responsibleperson=case when '"+RESPONSIBLE_PERSON+"' ='null' then null else '"+RESPONSIBLE_PERSON+"' end,TABCswgdmerge.arriveddate=case when '"+ARRIVED+"' ='null' then null else '"+ARRIVED+"' end,TABCswgdmerge.workorderstatus='4' where TABCswgdmerge.Relation_ID='"+RELATION_ID+"'","dataSource");
					//log.debug("读取中间表更新工单SQL执行结果:" + updatesamp);
					
					//查询现有当前工单编号是否存在现有工单
					def getSelectSQL = eliteBean.chaxun_sql("Select R1.Relation_ID, R3.Handlegroup_ID, R3.Handleby_ID, R3.CurstepStatus, R3.Curstep_Id From Tabcswgdmerge R1 Left Join Objective R2 On R1.Relation_Id = R2.Objective_Guid Left Join Elitetask R3 On R1.Relation_Id = R3.Objective_Guid Where R1.RELATION_ID = '" + RELATION_ID + "'", "dataSource");
					log.debug("查询当前同步的工单是否已经存在 SQL:" + getSelectSQL + " 结果数量:" + getSelectSQL.size());

					//获取当前工单编号对应的CRM唯一工单Guid Relation_ID
					String old_Relation_ID = null;
					String old_Handlegroup_ID = null;
					String old_Handleby_ID = null;
					String old_CurstepStatus = null;
					String old_Curstep_Id = null;

					if(getSelectSQL.size() > 0) {
						old_Relation_ID = getSelectSQL.get(0).get("Relation_ID");
						old_Handlegroup_ID = getSelectSQL.get(0).get("Handlegroup_ID");
						old_Handleby_ID = getSelectSQL.get(0).get("Handleby_ID");
						old_CurstepStatus = getSelectSQL.get(0).get("CurstepStatus");
						old_Curstep_Id = getSelectSQL.get(0).get("Curstep_Id");
					}else{
						old_Relation_ID = "";
						old_Handlegroup_ID = "";
						old_Handleby_ID = "";
						old_CurstepStatus = "";
						old_Curstep_Id = "";
					}
					log.debug("查询现有当前工单编号是否存在现有工单-结果old_Relation_ID:" + old_Relation_ID + " old_Handlegroup_ID:" + old_Handlegroup_ID + " old_Handleby_ID:" + old_Handleby_ID + " old_CurstepStatus:" + old_CurstepStatus + " old_Curstep_Id:" + old_Curstep_Id);

					
					// 查询下一个工单回访处理人
					def getRevSelectSQL = eliteBean.chaxun_sql("select Top 1 Rec_Guid, Rolegroup_ID, Staff_ID From SZWG_WO_RevisitStaff sr LEFT JOIN Group_Area ga on sr.AREA=ga.Area_guid Where Del_Flag = '0' and area_id='BA'","dataSource");
					log.debug("查询下一个工单回访处理人 SQL:" + getRevSelectSQL + " 结果数量:" + getRevSelectSQL.size());
	
					//获取查询下一个工单回访处理人
					String rev_Rec_Guid = null;
					String rev_Handlegroup_ID = null;
					String rev_Handleby_ID = null;
	
					if(getRevSelectSQL.size() > 0) {
						rev_Rec_Guid = getRevSelectSQL.get(0).get("Rec_Guid");
						rev_Handlegroup_ID = getRevSelectSQL.get(0).get("Rolegroup_ID");
						rev_Handleby_ID = getRevSelectSQL.get(0).get("Staff_ID");
					}else{
						rev_Rec_Guid = "";
						rev_Handlegroup_ID = "";
						rev_Handleby_ID = "";
					}
					
					String[] data1=new String[8];
					data1[0] = "insert into TABCswgdmerge_His select NEWID(), * from TABCswgdmerge where Relation_ID = '"+old_Relation_ID+"'";
					data1[1] = "update TABCswgdmerge set TABCswgdmerge.handledescription=case when '"+HANDLE_DESCRIPTION+"' ='null' then null else '"+HANDLE_DESCRIPTION+"' end,TABCswgdmerge.remarks=case when '"+REMARKS+"' ='null' then null else '"+REMARKS+"' end,TABCswgdmerge.starttime=case when '"+START_TIME+"' ='null' then null else '"+START_TIME+"' end,TABCswgdmerge.FINISHTIME=case when '"+FINISH_TIME+"' ='null' then null else '"+FINISH_TIME+"' end,TABCswgdmerge.workingday=case when '"+WORKING_DAY+"' ='null' then null else '"+WORKING_DAY+"' end,TABCswgdmerge.handlerby=case when '"+HANDLER+"' ='null' then null else '"+HANDLER+"' end,TABCswgdmerge.handlerphone=case when '"+HANDLER_PHONE+"' ='null' then null else '"+HANDLER_PHONE+"' end,TABCswgdmerge.responsibleperson=case when '"+RESPONSIBLE_PERSON+"' ='null' then null else '"+RESPONSIBLE_PERSON+"' end,TABCswgdmerge.arriveddate=case when '"+ARRIVED+"' ='null' then null else '"+ARRIVED+"' end,TABCswgdmerge.workorderstatus='4' where TABCswgdmerge.Relation_ID='"+old_Relation_ID+"'";
					data1[2] = "Insert Into Workorderstep3 (Workorder_Guid, Workorderstep_Guid, Oldhandleby_Id, Oldhandlegroup_Id, Handleby_Id, Handlegroup_Id, Workorderstepstatus, Comments, Modifieddate, Workorderstepid, Stepexpired, Child_Id, Stepstart, Cur_Expiredflag, Cur_Expiredlen, From_Stepidmap, To_Stepidmap, Workstarttime, Workendtime) Values ('" + old_Relation_ID + "', NewID(), '"+old_Handleby_ID+"', '"+old_Handlegroup_ID+"', '" + rev_Handleby_ID + "', '" + rev_Handlegroup_ID + "', 'SENDNS', '"+HANDLE_DESCRIPTION+"', Getdate(), Null, Getdate(), Null, Getdate(), 0, 0, '"+old_Curstep_Id+"', '0DKTUC', Getdate(), Getdate())";
					data1[3] = "Insert Into Workorderpath3 (Workorder_Guid, Workorderpath_Guid, Handlegroup, Handleby, Modifieddate, Fromstepid, Tostepid, Woframe_Id, Fromframeid, Handlerole_Id, From_Stepidmap, To_Stepidmap) Values ('" + old_Relation_ID + "', NewID(), 'SYSTEM', 'SELITE', Getdate(), Null, Null, 'ZHS14C', Null, Null, '"+old_Curstep_Id+"', '0DKTUC')";
					data1[4] = "Update Objective Set Handlegroup_Id = '" + rev_Handlegroup_ID + "', Handleby_Id = '" + rev_Handleby_ID + "', Objectivestatus = Objectivestatus, Map_Id = Map_Id, Curstep_Id_Map = '0DKTUC', Modifiedby = 'SELITE', Modifieddate = Getdate() Where Objective_Guid = '" + old_Relation_ID + "'";
					data1[5] = "Update Elitetask Set Handlegroup_Id = '" + rev_Handlegroup_ID + "', Handleby_Id = '" + rev_Handleby_ID + "', Map_Id = Map_Id, Curstepstatus = Curstepstatus, Curstep_Id = '0DKTUC', Curstep_Id_Map = '0DKTUC', Objectivesubstatus = Objectivesubstatus, Modifiedby = 'SELITE', Modifieddate = Getdate() Where Objective_Guid = '" + old_Relation_ID + "'";
					data1[6] = "Insert Into Szwg_Wo_Revisitstaff_List (List_Guid, Rec_Guid, Workorder_Id, Rolegroup_Id, Staff_Id, Createdtime) Values (NewID(), '" + rev_Rec_Guid + "', '" + old_Relation_ID + "', '" + rev_Handlegroup_ID + "', '" + rev_Handleby_ID + "', GetDate())";
					data1[7] = "Update SZWG_WO_RevisitStaff Set Revisit_Num = IsNull(Revisit_Num, 0) + 1 Where Rec_Guid = '" + rev_Rec_Guid + "'";
					log.debug("判断处理工单状态进行下一步处理SQL:" + data1.toString());
					int[] dataresult1 = eliteBean.updateBatch(data1, "dataSource");
					log.debug("判断处理工单状态进行下一步处理SQL结果:" + dataresult1.toString());
					
					GDCount++;

				}
				msg = "共有" + ListNum + "条归档工单，批量更新" + GDCount + "条！";
				log.debug("当前处理工单的结果:" + msg);
			}else {
				log.debug("当前没有需要更新的宝安归档工单数据");
			}
		} catch (Exception e) {
			log.error("当前归档状态工单SQL异常:" + e.getMessage());
		}
		//查询宝安工单处理时间为一个小时内的回退数据
		samp = "SELECT RELATION_ID,CONVERT(varchar(100), FINISH_TIME, 20) as FINISH_TIME,REBACK,CURSTATUS FROM OPENQUERY(BAGWMID,'SELECT * from BAGW_MID.SZSW_WORKFLOW where 1=1 and FINISH_TIME is not null and CURSTATUS=''GW006005''') a LEFT JOIN objective o on o.objective_guid=a.RELATION_ID where FINISH_TIME >DATEADD(HOUR, -1, GETDATE()) and o.curstep_id_map='VXGUM0'";
		log.debug("查询一个小时内的宝安工单回退状态数据SQL:" + samp);

		List BackBAGDList = eliteBean.chaxun_sql(samp, "dataSource");

		def BackListNum = BackBAGDList.size();

		if(BackListNum > 0){
			for(int i = 0; i<BackListNum; i++) {
				Map BAGDMap = BackBAGDList.get(i);

				def RELATION_ID = BAGDMap.get("RELATION_ID");
				def FINISH_TIME = BAGDMap.get("FINISH_TIME");
				def REBACK = BAGDMap.get("REBACK");
				def CURSTATUS = BAGDMap.get("CURSTATUS");

				//查询现有当前工单编号是否存在现有工单
				def getSelectSQL = eliteBean.chaxun_sql("Select R1.Relation_ID, R3.Handlegroup_ID, R3.Handleby_ID, R3.CurstepStatus, R3.Curstep_Id,R2.workorderid From Tabcswgdmerge R1 Left Join Objective R2 On R1.Relation_Id = R2.Objective_Guid Left Join Elitetask R3 On R1.Relation_Id = R3.Objective_Guid Where R1.Relation_ID = '" + RELATION_ID + "'", "dataSource");
				log.debug("查询当前同步的工单是否已经存在 SQL:" + getSelectSQL + " 结果数量:" + getSelectSQL.size());

				//获取当前工单编号对应的CRM唯一工单Guid Relation_ID
				String old_Relation_ID = null;
				String old_Handlegroup_ID = null;
				String old_Handleby_ID = null;
				String old_CurstepStatus = null;
				String old_Curstep_Id = null;
				String rev_Handlegroup_ID = null;
				String rev_Handleby_ID = null;
				String workorderid = null;

				if(getSelectSQL.size() > 0) {
					old_Relation_ID = getSelectSQL.get(0).get("Relation_ID");
					old_Handlegroup_ID = getSelectSQL.get(0).get("Handlegroup_ID");
					old_Handleby_ID = getSelectSQL.get(0).get("Handleby_ID");
					old_CurstepStatus = getSelectSQL.get(0).get("CurstepStatus");
					old_Curstep_Id = getSelectSQL.get(0).get("Curstep_Id");
					workorderid = getSelectSQL.get(0).get("workorderid");
					rev_Handlegroup_ID = "CW0003";
					rev_Handleby_ID = "";
				}else{
					old_Relation_ID = "";
					old_Handlegroup_ID = "";
					old_Handleby_ID = "";
					old_CurstepStatus = "";
					old_Curstep_Id = "";
					workorderid = "";
					rev_Handlegroup_ID = "";
					rev_Handleby_ID = "";
				}

				// 提前获取工单的上一步处理人
				def getOldSelectSQL = eliteBean.chaxun_sql("Select R1.Handlegroup, R1.Handleby, R1.From_Stepidmap From Workorderpath3 R1 Left Join (Select Workorder_Guid, Max(Modifieddate) Modifieddate From Workorderpath3 Where To_Stepidmap = 'VXGUM0' And Workorder_Guid = '" + old_Relation_ID + "' Group By Workorder_Guid) R2 On R1.Workorder_Guid = R2.Workorder_Guid And R1.Modifieddate = R2.Modifieddate Where R1.Workorder_Guid = '" + old_Relation_ID + "' And R2.Workorder_Guid Is Not Null", "dataSource");
				log.debug("查询获取工单的上一步处理人 SQL:" + getOldSelectSQL + " 结果数量:" + getOldSelectSQL.size());

				//获取当前工单编号对应的CRM上一步处理信息
				String before_Handlegroup_ID = null;
				String before_Handleby_ID = null;
				String before_From_Stepidmap = null;

				if(getOldSelectSQL.size() > 0) {
					before_Handlegroup_ID = getOldSelectSQL.get(0).get("Handlegroup");
					before_Handleby_ID = getOldSelectSQL.get(0).get("Handleby");
					before_From_Stepidmap = getOldSelectSQL.get(0).get("From_Stepidmap");
				}else{
					before_Handlegroup_ID = null;
					before_Handleby_ID = null;
					before_From_Stepidmap = null;
				}

				log.debug("当前处理状态变更的工单在CRM系统唯一Relation_ID:" + old_Relation_ID + " CURSTATUS:" + CURSTATUS);


				String dealResult = "";
				if(old_Relation_ID != null && old_Relation_ID.length() > 0 && old_Curstep_Id=='VXGUM0') {
					try {
						// 3-回退 将工单回退到上一步工单处理人一般是接单坐席
						String[] data1=new String[6];
						data1[0] = "Insert Into Workorderstep3 (Workorder_Guid, Workorderstep_Guid, Oldhandleby_Id, Oldhandlegroup_Id, Handleby_Id, Handlegroup_Id, Workorderstepstatus, Comments, Modifieddate, Workorderstepid, Stepexpired, Child_Id, Stepstart, Cur_Expiredflag, Cur_Expiredlen, From_Stepidmap, To_Stepidmap, Workstarttime, Workendtime) Values ('" + old_Relation_ID + "', NewID(), '"+old_Handleby_ID+"', '"+old_Handlegroup_ID+"', '" + before_Handleby_ID + "', '" + before_Handlegroup_ID + "', 'BACK', '"+REBACK+"', Getdate(), Null, Getdate(), Null, Getdate(), 0, 0, '"+old_Curstep_Id+"', '" + before_From_Stepidmap + "', Getdate(), Getdate())";
						data1[1] = "Delete From Workorderpath3 Where Workorder_Guid = '" + old_Relation_ID + "' And From_Stepidmap = '" + before_From_Stepidmap + "' And To_stepidmap = '" + old_Curstep_Id + "'";
						data1[2] = "Update Objective Set Handlegroup_Id = '" + before_Handlegroup_ID + "', Handleby_Id = '" + before_Handleby_ID + "', Objectivestatus = Objectivestatus, Map_Id = Map_Id, Curstep_Id_Map = '" + before_From_Stepidmap + "', Modifiedby = 'SELITE', Modifieddate = Getdate() Where Objective_Guid = '" + old_Relation_ID + "'";
						data1[3] = "Update Elitetask Set Handlegroup_Id = '" + before_Handlegroup_ID + "', Handleby_Id = '" + before_Handleby_ID + "', Map_Id = Map_Id, Curstep_Id = '" + before_From_Stepidmap + "', Curstep_Id_Map = '" + before_From_Stepidmap + "', Objectivesubstatus = Objectivesubstatus, Modifiedby = 'SELITE', Modifieddate = Getdate() Where Objective_Guid = '" + old_Relation_ID + "'";
						data1[4] = "update TABCswgdmerge set TABCswgdmerge.workorderstatus='3' where TABCswgdmerge.Relation_ID='"+RELATION_ID+"'";
						data1[5] = "INSERT INTO hs_spring_screen (noticeId, noticeGroup, noticeBy, createGroup, createBy, noticeContent, createddate, limitCount, loadCount, noticeType, objective_guid) VALUES(NEWID(), '"+before_Handlegroup_ID+"', '"+before_Handleby_ID+"', 'SYSTEM', 'SELITE','工单编号：【"+workorderid+"】已退回，请及时处理！ 原因：【"+REBACK+"】' ,GETDATE(), 1, 0, '退单弹窗', '"+RELATION_ID+"')";
						log.debug("判断处理工单状态进行下一步处理SQL:" + data1.toString());
						int[] dataresult1 = eliteBean.updateBatch(data1, "dataSource");
						log.debug("判断处理工单状态进行下一步处理SQL结果:" + dataresult1.toString());

					} catch (Exception e) {

						log.error("当前工单回退变更的SQL异常:" + e.getMessage());
					}
					GDCount++;
				}else {
					log.debug("当前处理结果变更的工单在CRM系统不存在ID:" + RELATION_ID);

				}
				

			}
			msg = "共有" + BackListNum + "条回退工单，批量更新" + GDCount + "条！";
			log.debug("当前处理工单的结果:" + msg);
		}else {
			log.debug("当前没有需要更新的宝安回退工单数据");
		}


	}
}